﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Text;
using System.Data;

namespace Our.Umbraco.uDbCompare
{
    #region Enums

    public enum DbCompareOptions
    {
        Full,
        Left,
        Right,
        Difference
    }

    public enum DbCompareLevel
    {
        Name,
        NameProperties,
        NamePropertiesChildren
    }

    public enum DbCompareMatch
    {
        Matching,
        NonMatching,
        Missing
    }

    public enum DbCompareItem
    {
        MediaTypeStructure,
        MediaTypeProperty,
        DocTypeTemplate,
        DocTypeStructure,
        DocTypeProperty,
        DataType,
        RelationType,
        Template,
        Dictionary,
        Macro,
        MemberType,
        MemberGroup,
        Package
    }

    #endregion

    public struct DbCompareQuery
    {
        public string key { get; set; }
        public List<string> fields { get; set; }
        public string select { get; set; }
        public string childKey { get; set; }
        public bool childKeyContainsDelimiter { get; set; }
        public List<string> childFields { get; set; }
        public bool createChildren { get; set; }
    }

    public class uDbCompare
    {
        #region Static Properties

        public static string fieldName = "Name";
        public static string left = "left";
        public static string right = "right";
        public static string leftDataMissing = "leftDataMissing";
        public static string rightDataMissing = "rightDataMissing";
        //public static string deepCompare = "deepCompare";
        private static string preValueDelimiter = "|";
        private static string preValueFieldName = "preValue";


        public static string CurrentConnectionString
        {
            get { return System.Configuration.ConfigurationSettings.AppSettings["umbracoDbDSN"]; }
        }

        public static string RemoteConnectionString
        {
            get
            {
                string conn = System.Web.HttpContext.Current.Session["remoteConnectionString"] as string;
                return conn;

            }
            set { System.Web.HttpContext.Current.Session["remoteConnectionString"] = value; }
        }

        #endregion

        #region Properties

        /// <summary>
        /// this is a list of fields in the current table that have values and the values should match perhaps
        /// </summary>
        private Dictionary<string, string> dataValues;
        public string Key { get; set; }
        public bool KeyContainsDelimeter { get; set; }

        public Dictionary<string, string> DataValues
        {
            get 
            {
                if (dataValues == null)
                    dataValues = new Dictionary<string, string>();
                return dataValues; 
            }
        }

        private List<uDbCompare> children;
        public List<uDbCompare> Children
        {
            get
            {
                if (children == null)
                    children = new List<uDbCompare>();
                return children;
            }
        }

        #endregion

        public void AddChild(uDbCompare dbCompare)
        {
            if (children == null)
                children = new List<uDbCompare>();
            children.Add(dbCompare);
        }

        public void AddDataValue(string key, string value)
        {
            if (!DataValues.ContainsKey(key))
                dataValues.Add(key, value);
            else
                dataValues[key] = value;
        }

        #region Comparison

        /// <summary>
        /// Compare an item
        /// </summary>
        /// <param name="itemToCompare"></param>
        /// <returns></returns>
        public DbCompareMatch Compare(uDbCompare itemToCompare, DbCompareLevel compareLevel)
        {
            DbCompareMatch? match = null;
            if (Key == itemToCompare.Key)
                match = DbCompareMatch.Matching;
            else
                match = DbCompareMatch.NonMatching;

            if (compareLevel == DbCompareLevel.Name || match == DbCompareMatch.NonMatching)
            {
                return match.Value;
            }

            // If there aren't an equal number of items in the dictionary, there is obviously something missing
            if (itemToCompare.DataValues.Count != DataValues.Count)
                return DbCompareMatch.Missing;

            foreach (string field in DataValues.Keys)
            {
                // There's the same number of items in the dictionary, but just make sure that they are actually the same key name
                if (!itemToCompare.DataValues.ContainsKey(field))
                    return DbCompareMatch.Missing;

                // Make sure the value matches now
                //if (DataValues[field] != itemToCompare.DataValues[field])
                if (!Compare(field, DataValues[field], itemToCompare.DataValues[field]))
                    return DbCompareMatch.NonMatching;
                
                    
            }

            if (compareLevel == DbCompareLevel.NamePropertiesChildren)
            {
                DataTable dt = Compare(DbCompareOptions.Difference, compareLevel, Children, itemToCompare.Children);
                if (dt.Rows.Count > 0)
                    return DbCompareMatch.NonMatching;
            }

            // If there were no missing fields and all the values matched, then it matches
            return DbCompareMatch.Matching;
        }

        #endregion

        #region Old Code
        /*
        public static List<string> GetValuesFromTableByTable(string connectionString, string key,  string table)
        {
            string commandText = string.Format("select {0} from {1}", GetFormattedColumnName(key), table);
            return GetValuesFromTableBySelect(connectionString, key, commandText);
        }

        public static List<string> GetValuesFromTableBySelect(string connectionString, string key, string select)
        {
            List<string> values = new List<string>();

            using (OleDbConnection conn = new OleDbConnection(FormatConnectionString(connectionString)))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(select, conn);
                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    values.Add(reader[key].ToString());
                }
            }
            return values;
        }
        */

        #endregion

        #region Private Static Helper Methods


        private static string GetFormattedColumnName(string columnName)
        {
            if (columnName == "key" || columnName == "group")
                columnName = "[" + columnName + "]";
            return columnName;
        }

        private static void SetFormattedSelect(ref DbCompareQuery query, string table)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("select {0}", GetFormattedColumnName(query.key)));
            foreach (string field in query.fields)
            {
                if (field != query.key)
                    sb.Append(string.Format(",{0}", GetFormattedColumnName(field)));
            }
            sb.Append(string.Format(" from {0}", table));

            query.select = sb.ToString();
        }

        private static void AddToTable(DataTable dt, string name, DbCompareMatch leftMatch, DbCompareMatch rightMatch)
        {
            DataRow dr = dt.NewRow();
            dr[fieldName] = name;
            dr[left] = leftMatch;
            dr[right] = rightMatch;
            dt.Rows.Add(dr);
        }

        private static void AddToTable(DataTable dt, string name, string leftData, string rightData)
        {
            DataRow dr = dt.NewRow();
            dr[fieldName] = name;
            dr[left] = leftData;
            dr[right] = rightData;
            dr[leftDataMissing] = false;
            dr[rightDataMissing] = false;
            dt.Rows.Add(dr);
        }

        #endregion

        #region Public Static Helper Methods

        public static string FormatConnectionString(string connectionString)
        {
            if (!connectionString.Contains("Provider"))
                connectionString += ";Provider=SQLOLEDB";
            return connectionString;
        }

        public static string GetServerName(string connectionString, string notFound)
        {
            string serverName = null;
            string server = null;
            string database = null;
            if (!string.IsNullOrEmpty(connectionString))
            {
                string[] keyValuePairs = connectionString.Split(';');
                foreach (string keyValuePair in keyValuePairs)
                {
                    string[] keyValues = keyValuePair.Split('=');
                    if (keyValues[0].Equals("server", StringComparison.InvariantCultureIgnoreCase))
                        server = keyValues[1];
                    if (keyValues[0].Equals("database", StringComparison.InvariantCultureIgnoreCase))
                        database = keyValues[1];
                }

                serverName = string.Format("{0} -> {1}", server, database);
            }

            if (string.IsNullOrEmpty(server) && string.IsNullOrEmpty(database))
                serverName = notFound;
            return serverName;
        }

        public static bool IsValidDbConnection(bool useRemoteConnection)
        {
            string connectionString = CurrentConnectionString;
            if (useRemoteConnection)
                connectionString = RemoteConnectionString;

            if (connectionString.Contains("{"))
                return false;

            return true;
        }

        #endregion

        #region DataTable loading

        public static List<uDbCompare> GetValuesFromTableByTable(string connectionString, DbCompareQuery query, string table, DbCompareItem compareItem)
        {
            SetFormattedSelect(ref query, table);
            return GetValuesFromTableBySelect(connectionString, query, compareItem);
        }

        public static DataTable QueryDb(string query, string connectionString)
        {
            return QueryDb(query, connectionString, null);
        }

        public static DataTable QueryDb(string query, string connectionString, OleDbParameter[] parameters)
        {
            DataTable dt = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(FormatConnectionString(connectionString)))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(query, conn);
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                OleDbDataReader reader = cmd.ExecuteReader();

                dt.Load(reader);
            }
            foreach (DataColumn column in dt.Columns)
            {
                column.ReadOnly = false;
            }
            return dt;
        }

        public static List<uDbCompare> GetValuesFromTableBySelect(string connectionString, DbCompareQuery query, DbCompareItem compareItem)
        {
            List<uDbCompare> values = new List<uDbCompare>();

            using (OleDbConnection conn = new OleDbConnection(FormatConnectionString(connectionString)))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(query.select, conn);
                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    uDbCompare compare = new uDbCompare();
                    string key = reader[query.key].ToString();
                    compare.Key = key;

                    uDbCompare parentCompare = values.Where(x => x.Key == key).FirstOrDefault();

                    if (!query.createChildren || parentCompare == null)
                    {
                        foreach (string field in query.fields)
                        {
                            compare.AddDataValue(field, reader[field].ToString());
                        }
                        values.Add(compare);
                        parentCompare = compare;
                    }

                    if (query.createChildren)
                    {
                        uDbCompare childCompare = new uDbCompare();
                        childCompare.Key = reader[query.childKey].ToString();
                        
                        if (query.childKeyContainsDelimiter)
                        {
                            childCompare.Key = DelimitedListWithoutNumeric(childCompare.Key, preValueDelimiter);
                            if (childCompare.Key.Contains(preValueDelimiter))
                                childCompare.KeyContainsDelimeter = true;
                        }

                        foreach (string field in query.childFields)
                        {
                            childCompare.AddDataValue(field, reader[field].ToString());
                        }
                        parentCompare.AddChild(childCompare);
                    }
                }
            }
            return values;

        }

        #endregion

        #region Static Comparision

        private static DataTable Compare(DbCompareOptions compareOptions, DbCompareLevel compareLevel, List<uDbCompare> leftCompare, List<uDbCompare> rightCompare)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(fieldName, typeof(string));
            dt.Columns.Add(left, typeof(DbCompareMatch));
            dt.Columns.Add(right, typeof(DbCompareMatch));
            //dt.Columns.Add(deepCompare, typeof(bool));

            // Loop left and try to find an item with the same key on the right
            // if not found, then missing
            // otherwise, do the compare
            foreach (var leftItem in leftCompare)
            {
                var rightItem = rightCompare.Where(x => x.Key == leftItem.Key).FirstOrDefault();
                if (rightItem == null)
                {
                    // For a right compare, we don't want this case
                    if (compareOptions != DbCompareOptions.Right)
                        AddToTable(dt, leftItem.Key, DbCompareMatch.Matching, DbCompareMatch.Missing);
                }
                else
                {
                    var match = leftItem.Compare(rightItem, compareLevel);
                    switch (match)
                    {
                        case DbCompareMatch.Matching:
                            // For difference, we shouldn't add this case
                            if (compareOptions != DbCompareOptions.Difference)
                                AddToTable(dt, leftItem.Key, DbCompareMatch.Matching, DbCompareMatch.Matching);
                            break;
                        case DbCompareMatch.Missing:
                            // For a right compare, we don't want this case
                            if (compareOptions != DbCompareOptions.Right)
                                AddToTable(dt, leftItem.Key, DbCompareMatch.Matching, DbCompareMatch.Missing);
                            break;
                        case DbCompareMatch.NonMatching:
                            AddToTable(dt, leftItem.Key, DbCompareMatch.NonMatching, DbCompareMatch.NonMatching);
                            break;
                    }
                }
            }


            // Loop right and only add items that don't exist on the left
            // We have already handled all cases where the left item & right item have the same key
            foreach (var rightItem in rightCompare)
            {
                var leftItem = leftCompare.Where(x => x.Key == rightItem.Key).FirstOrDefault();
                if (leftItem == null)
                {
                    if (compareOptions != DbCompareOptions.Left)
                        AddToTable(dt, rightItem.Key, DbCompareMatch.Missing, DbCompareMatch.Matching);
                }
            }
            dt.DefaultView.Sort = fieldName + " asc";
            return dt;
        }

        public static DataTable Compare(DbCompareItem compareItem, DbCompareOptions compareOptions, DbCompareLevel compareLevel,
                                        string leftConnectionString, string rightConnectionString)
        {
            DbCompareQuery query = GetSelect(compareItem, compareLevel);

            var leftCompare = GetValuesFromTableBySelect(leftConnectionString, query, compareItem);
            var rightCompare = GetValuesFromTableBySelect(rightConnectionString, query, compareItem);

            return Compare(compareOptions, compareLevel, leftCompare, rightCompare);
        }

        public static bool Compare(string field, string left, string right)
        {
            if (field != preValueFieldName || !left.Contains(preValueDelimiter) || !right.Contains(preValueDelimiter))
                return (left == right);
            else
                return (DelimitedListWithoutNumeric(left, preValueDelimiter) == DelimitedListWithoutNumeric(right, preValueDelimiter));
        }

        private static string DelimitedListWithoutNumeric(string list, string delimiter)
        {
            if (!list.Contains(delimiter))
                return list;
            List<string> nonNumericList = new List<string>();
            string[] delimiters = new string[] { delimiter };
            string[] items = list.Split(delimiters, StringSplitOptions.None);
            foreach (var item in items)
            {
                int numeric;
                if (!Int32.TryParse(item, out numeric))
                    nonNumericList.Add(item);
            }
            return String.Join(delimiter, nonNumericList.ToArray());
        }

        private static void CompareDetails(DataTable dt, uDbCompare leftCompare, uDbCompare rightCompare)
        {
            if (leftCompare != null)
            {
                foreach (var dataValue in leftCompare.DataValues)
                {
                    if (rightCompare == null)
                    {
                        AddToTable(dt, dataValue.Key, dataValue.Value, string.Empty);
                    }
                    else
                    {
                        var rightItem = rightCompare.DataValues.Where(x => x.Key == dataValue.Key).FirstOrDefault();
                        AddToTable(dt, dataValue.Key, dataValue.Value, rightItem.Value);
                    }
                }
            }

            if (rightCompare != null)
            {
                foreach (var dataValue in rightCompare.DataValues)
                {
                    if (leftCompare == null)
                    {
                        AddToTable(dt, dataValue.Key, string.Empty, dataValue.Value);
                    }
                    /* prevent double add
                    else 
                    {
                        var leftItem = leftCompare.DataValues.Where(x => x.Key == dataValue.Key).FirstOrDefault();
                        AddToTable(dt, dataValue.Key, leftItem.Value, dataValue.Value);
                    }
                     */
                }
            }

        }

        public static DataTable CompareDetails(DbCompareItem compareItem, string key,
                                        string leftConnectionString, string rightConnectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(fieldName, typeof(string));
            dt.Columns.Add(left, typeof(string));
            dt.Columns.Add(right, typeof(string));
            dt.Columns.Add(leftDataMissing, typeof(bool));
            dt.Columns.Add(rightDataMissing, typeof(bool));
            //dt.Columns.Add(deepCompare, typeof(bool));

            // for details, we want a full compare
            DbCompareQuery query = GetSelect(compareItem, DbCompareLevel.NamePropertiesChildren);

            // TODO: Not the most efficient - would be better to pass in the key to GetValues method
            var leftCompare = GetValuesFromTableBySelect(leftConnectionString, query, compareItem).Where(x => x.Key == key).FirstOrDefault();
            var rightCompare = GetValuesFromTableBySelect(rightConnectionString, query, compareItem).Where(x => x.Key == key).FirstOrDefault();

            CompareDetails(dt, leftCompare, rightCompare);

            if (leftCompare != null)
            {
                foreach (var leftChild in leftCompare.Children)
                {
                    if (rightCompare == null)
                    {
                        CompareDetails(dt, leftChild, null);
                    }
                    else
                    {
                        var rightChild = rightCompare.Children.Where(x => x.Key == leftChild.Key).FirstOrDefault();
                        
                        // If there's only 1 child and there's a delimiter, then we probably want to use that
                        if (rightChild == null && leftChild.KeyContainsDelimeter && rightCompare.Children.Count == 1)
                            rightChild = rightCompare.Children.First();

                        CompareDetails(dt, leftChild, rightChild);
                    }
                }
            }

            if (rightCompare != null)
            {
                foreach (var rightChild in rightCompare.Children)
                {
                    if (leftCompare == null)
                    {
                        CompareDetails(dt, null, rightChild);
                    }
                    else
                    {
                        var leftChild = leftCompare.Children.Where(x => x.Key == rightChild.Key).FirstOrDefault();
                        
                        // Make sure we haven't haven't already added this above with the guess when there's a key delimiter & 1 child
                        if (leftChild == null && !rightChild.KeyContainsDelimeter && rightChild.Children.Count != 1)
                            CompareDetails(dt, leftChild, rightChild);
                    }
                }
            }

            if (leftCompare == null)
            {
                foreach (DataRow row in dt.Rows)
                {
                    row[leftDataMissing] = true;
                }
            }

            if (rightCompare == null)
            {
                foreach (DataRow row in dt.Rows)
                {
                    row[rightDataMissing] = true;
                }
            }

            return dt;

        }

        #endregion


        #region Umbraco specific

        private static DbCompareQuery GetSelect(DbCompareItem compareItem, DbCompareLevel compareLevel)
        {
            DbCompareQuery query = new DbCompareQuery();
            switch(compareItem)
            {
                case DbCompareItem.DocTypeTemplate:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "icon", "thumbnail", "description", "parentAlias" };
                            query.select = @"select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias, 
                                                    dt.templateNodeId, dt.IsDefault, Template.alias as templateAlias
                                                from cmsContentType c 
                                                join umbracoNode n on c.nodeId = n.Id                                                 
                                                left outer join cmsContentType p on c.masterContentType = p.nodeId 
                                                left outer join cmsDocumentType dt on c.nodeid = dt.contenttypenodeid 
                                                left outer join umbracoNode TemplateNode on dt.templateNodeId = TemplateNode.id 
                                                left outer join cmsTemplate Template on TemplateNode.id = Template.nodeId
                                                where c.MasterContentType is not null 
                                                    and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            query.createChildren = true;
                            query.childKey = "templateAlias";
                            query.childFields = new List<string>() { "templateAlias", "IsDefault" };

                            break;
                    }
                    
                    //select = GetFormattedSelect(key, fields, "cmsContentType");
                    
                    break;

                case DbCompareItem.DocTypeStructure:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "icon", "thumbnail", "description", "parentAlias" };
                            query.select = @"select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias, 
                                                    ctChild.alias as ChildAlias
                                                from cmsContentType c 
                                                join umbracoNode n on c.nodeId = n.Id                                                 
                                                left outer join cmsContentType p on c.masterContentType = p.nodeId 
                                                left outer join cmsContentTypeAllowedContentType ct on c.nodeid = ct.id
                                                left outer join cmsContentType ctChild on ct.allowedid = ctchild.nodeid
                                                where c.MasterContentType is not null 
                                                    and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            query.createChildren = true;
                            query.childKey = "ChildAlias";
                            query.childFields = new List<string>() { "ChildAlias" };

                            break;
                    }

                    //select = GetFormattedSelect(key, fields, "cmsContentType");

                    break;

                case DbCompareItem.DocTypeProperty:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where c.MasterContentType is not null and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "icon", "thumbnail", "description", "parentAlias" };
                            query.select = @"select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias, 
                                                    pt.alias as propertyAlias, pt.name as propertyName, 
                                                    dt.dbType as propertyDbType, dataTypeNode.text as propertyControlType
                                                from cmsContentType c 
                                                join umbracoNode n on c.nodeId = n.Id                                                 
                                                left outer join cmsContentType p on c.masterContentType = p.nodeId 
                                                left outer join cmsPropertyType pt on c.NodeId = pt.contentTypeId 
												left outer join cmsdatatype dt on pt.dataTypeId = dt.nodeid
												left outer join umbracoNode dataTypeNode on dt.nodeid = dataTypeNode.id
                                                where c.MasterContentType is not null 
                                                    and n.nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB'";
                            query.createChildren = true;
                            query.childKey = "propertyAlias";
                            query.childFields = new List<string>() { "propertyAlias", "propertyName", "propertyDbType", "propertyControlType" };

                            break;
                    }

                    //select = GetFormattedSelect(key, fields, "cmsContentType");

                    break;

                case DbCompareItem.MediaTypeStructure:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "icon", "thumbnail", "description", "parentAlias" };
                            query.select = @"select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias, 
                                                    ctChild.alias as ChildAlias
                                                from cmsContentType c 
                                                join umbracoNode n on c.nodeId = n.Id                                                 
                                                left outer join cmsContentType p on c.masterContentType = p.nodeId 
                                                left outer join cmsContentTypeAllowedContentType ct on c.nodeid = ct.id
                                                left outer join cmsContentType ctChild on ct.allowedid = ctchild.nodeid
                                                where n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'";
                            query.createChildren = true;
                            query.childKey = "ChildAlias";
                            query.childFields = new List<string>() { "ChildAlias" };

                            break;
                    }

                    //select = GetFormattedSelect(key, fields, "cmsContentType");
                    
                    break;

                case DbCompareItem.MediaTypeProperty:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "icon", "thumbnail", "description", "parentAlias" };
                            query.select = @"select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias, 
                                                    pt.alias as propertyAlias, pt.name as propertyName, 
                                                    dt.dbType as propertyDbType, dataTypeNode.text as propertyControlType
                                                from cmsContentType c 
                                                join umbracoNode n on c.nodeId = n.Id                                                 
                                                left outer join cmsContentType p on c.masterContentType = p.nodeId 
                                                left outer join cmsPropertyType pt on c.NodeId = pt.contentTypeId 
												left outer join cmsdatatype dt on pt.dataTypeId = dt.nodeid
												left outer join umbracoNode dataTypeNode on dt.nodeid = dataTypeNode.id
                                                where n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'";
                            query.createChildren = true;
                            query.childKey = "propertyAlias";
                            query.childFields = new List<string>() { "propertyAlias", "propertyName", "propertyDbType", "propertyControlType" };

                            break;
                    }

                    //select = GetFormattedSelect(key, fields, "cmsContentType");

                    break;

                case DbCompareItem.MemberType:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where n.nodeObjectType = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43'";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            query.select = "select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias from cmsContentType c left outer join cmsContentType p on c.masterContentType = p.nodeId join umbracoNode n on c.nodeId = n.Id where n.nodeObjectType = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43'";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "icon", "thumbnail", "description", "parentAlias" };
                            query.select = @"select c.alias, c.icon, c.thumbnail, c.description, p.alias as parentAlias, 
                                                    pt.alias as propertyAlias, pt.name as propertyName, 
                                                    dt.dbType as propertyDbType, dataTypeNode.text as propertyControlType
                                                from cmsContentType c 
                                                join umbracoNode n on c.nodeId = n.Id                                                 
                                                left outer join cmsContentType p on c.masterContentType = p.nodeId 
                                                left outer join cmsPropertyType pt on c.NodeId = pt.contentTypeId 
												left outer join cmsdatatype dt on pt.dataTypeId = dt.nodeid
												left outer join umbracoNode dataTypeNode on dt.nodeid = dataTypeNode.id
                                                where n.nodeObjectType = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43'";
                            query.createChildren = true;
                            query.childKey = "propertyAlias";
                            query.childFields = new List<string>() { "propertyAlias", "propertyName", "propertyDbType", "propertyControlType" };

                            break;
                    }

                    //select = GetFormattedSelect(key, fields, "cmsContentType");

                    break;

                case DbCompareItem.MemberGroup:
                    query.key = "text";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                        case DbCompareLevel.NameProperties:
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "text" };
                            query.select = "select text from umbracoNode n where n.nodeObjectType = '366E63B9-880F-4E13-A61C-98069B029728'";
                            break;
                    }

                    break;

                case DbCompareItem.DataType:
                    query.key = "text";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "text" };
                            query.select = "select n.text, dbtype, controlid from cmsdatatype dt join umbraconode n on dt.nodeid=n.id";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "text", "dbtype", "controlid" };
                            query.select = "select n.text, dbtype, controlid from cmsdatatype dt join umbraconode n on dt.nodeid=n.id";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "text", "dbtype", "controlid" };
                            query.select = @"select n.text, dbtype, controlid, preValue.value as " + preValueFieldName + @"
                                                ,case when charindex('|', preValue.value) = 0 then preValue.value else substring(preValue.value, 0, charindex('|', preValue.value)) end as preValueKey
                                                from cmsdatatype dt join umbraconode n on dt.nodeid=n.id
                                                left outer join cmsDataTypePreValues preValue on preValue.dataTypeNodeId = dt.nodeId";
                            query.createChildren = true;
                            query.childKey = preValueFieldName;
                            query.childKeyContainsDelimiter = true;
                            query.childFields = new List<string>() { preValueFieldName };
                            break;
                    }
                    
                    break;

                case DbCompareItem.Dictionary:
                    query.key = "key";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "key" };
                            query.select = "select d.[key], p.[key] as parentKey from cmsdictionary d left outer join cmsDictionary p on d.parent = p.id";
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "key", "parentKey" };
                            query.select = "select d.[key], p.[key] as parentKey from cmsdictionary d left outer join cmsDictionary p on d.parent = p.id";
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            // TODO: add in dictionaries
                            query.fields = new List<string>() { "key", "parentKey" };
                            query.select = "select d.[key], p.[key] as parentKey, l.languageid, l.value from cmsdictionary d left outer join cmsDictionary p on d.parent = p.id left outer join cmsLanguageText l on d.id = l.uniqueid";
                            query.createChildren = true;
                            query.childKey = "languageid";
                            query.childFields = new List<string>() { "languageid", "value" };
                            break;
                    }
                    
                    break;

                case DbCompareItem.Template:
                    query.key = "alias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "alias", "parentAlias" };
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "parentAlias", "design" };
                            break;
                    }
                    
                    //select = GetFormattedSelect(key, fields, "cmsTemplate");
                    query.select = "select t.alias, t.design, p.alias as 'parentAlias' from cmsTemplate t left outer join cmsTemplate p on t.master = p.nodeid";
                    break;

                case DbCompareItem.Macro:
                    query.key = "macroAlias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "macroAlias" };
                            SetFormattedSelect(ref query, "cmsMacro");
                            break;
                        case DbCompareLevel.NameProperties:
                            query.fields = new List<string>() { "macroAlias", "macroxslt", "macroscripttype", "macroScriptAssembly", "macroPython", "macroUseInEditor", "macroRefreshRate", "macroCacheByPage", "macroCachePersonalized", "macroDontRender" };
                            SetFormattedSelect(ref query, "cmsMacro");
                            break;
                        case DbCompareLevel.NamePropertiesChildren:
                            //TODO: add "macropropertyalias" };
                            query.fields = new List<string>() { "macroAlias", "macroxslt", "macroscripttype", "macroScriptAssembly", "macroPython", "macroUseInEditor", "macroRefreshRate", "macroCacheByPage", "macroCachePersonalized", "macroDontRender" };
                            query.select = "select m.*, mp.macropropertyalias from cmsmacro m left outer join cmsmacroproperty mp on m.id = mp.macro";
                            query.childKey = "macropropertyalias";
                            query.childFields = new List<string>() { "macropropertyalias" };
                            query.createChildren = true;
                            break;
                    }
                    break;

                case DbCompareItem.RelationType:
                    query.key = "alias";
                    // parent & child objectype are predefined values
                    // http://our.umbraco.org/wiki/reference/api-cheatsheet/relationtypes-and-relations/object-guids-for-creating-relation-types
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "alias" };
                            break;
                        case DbCompareLevel.NameProperties:
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "alias", "parentObjectType", "childObjectType", "dual" };
                            break;
                    }
                    
                    SetFormattedSelect(ref query, "umbracoRelationType");
                    break;

                case DbCompareItem.Package:
                    query.key = "treeAlias";
                    switch (compareLevel)
                    {
                        case DbCompareLevel.Name:
                            query.fields = new List<string>() { "treeAlias" };
                            break;
                        case DbCompareLevel.NameProperties:
                        case DbCompareLevel.NamePropertiesChildren:
                            query.fields = new List<string>() { "treeAlias", "treeTitle", "treeHandlerAssembly", "treeHandlerType" };
                            break;
                    }

                    SetFormattedSelect(ref query, "umbracoapptree");
                    break;
            }
            return query;
        }

        #endregion

    }
}
